<?php
/**
 * IG_Export_Excel
 *
 * @author maomao.thx@gmail.com
 * @desc php生成excel类函数 支持多工作薄(数据分卷技术)
 * @copyright Copyright (c) 2009 Green Shore (http://www.oaiwgame.com)
 * @version $Id: Excel.php 624 2011-06-09 07:58:45Z lirong $
 */
Class IG_Export_Excel implements IG_Export_Interface
{

    private $_header             = "<?xml version=\"1.0\" encoding=\"%s\"?>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";
    private $_footer             = "</Workbook>";
    private $_alignLeft          = "<Style ss:ID=\"s62\"><Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/></Style>";
    private $_alignCenter        = "<Style ss:ID=\"s63\"><Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/></Style>";
    private $_alignRight         = "<Style ss:ID=\"s64\"><Alignment ss:Horizontal=\"Right\" ss:Vertical=\"Center\"/></Style>";
    private $_alignBold          = "<Style ss:ID=\"s65\"><Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Color=\"#000000\" ss:Bold=\"1\"/></Style>";
    private $_alignDefault       = "<Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Horizontal=\"%s\" ss:Vertical=\"Center\"/><Borders/><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"11\" ss:Color=\"#000000\"/><Interior/><NumberFormat/><Protection/></Style>";
    private $_charset            = "UTF-8";
    private $_convert            = "convert";
    private static $_preWorkBook = NULL;
    private $_line               = NULL;
    private $_column             = NULL;
    private $_columnType         = NULL;
    private $_title              = NULL;
    private $_align              = "Left";
    private $_defaultHeight      = 13.5;
    private $_defaultWidth       =  54;
    private $_sheets             = NULL;
    private $_heads              = NULL;

    /**
     * @desc 构造方法
     * @param string $charset 字符编码
     */
    public function __construct($charset = 'UTF-8')
    {
        $this->_charset = $charset;
    }

    /**
     * @desc 释放可用资源
     * @return null
     */
    public function release()
    {
        unset($this->_line, $this->_column, $this->_heads, $this->_sheets,
              $this->_styles, $this->_style, $this->_title, self::$_preWorkBook);
    }

    /**
     * @desc 数组行转换函数
     * @param array $array
     */
    protected function getLine($array)
    {
        $_temp = "<Row ss:AutoFitHeight=\"0\">";
        foreach ($array as $key => $val){
            #读取指定数据类型,默认String
            $_type = !empty($this->_columnType) && isset($this->_columnType) ?
                     !empty($this->_columnType[$key])&&isset($this->_columnType) ?
                     $this->_columnType[$key] : "String" : "String" ;
            $_temp .= "<Cell><Data ss:Type=\"{$_type}\">{$this->convert($val)}</Data></Cell>";
        }
        $_temp .= "</Row>";
        return $_temp;
    }

    /**
     * @desc 添加表格头
     * @param array $array
     * @param string $sheet 工作表名
     * @exception $array 不能为空
     */
    public function addHead($array, $sheet = "sheet1")
    {
        $this->_line[$sheet][0]         = $this->getLine($array);
        $this->_title[$sheet]['width']  = count($array)-1;
        $this->_sheets[]                = $sheet;
        $this->_heads[$sheet][0]        = $array;
    }

    /**
     * @desc 添加行
     * @param array $array
     * @param string $sheet
     * @param boolean $isErrorReport
     */
    public function addRow($array, $sheet = "sheet1", $isErrorReport=true)
    {
        if ($isErrorReport) {
            if (empty($array) || !isset($array) || count($array) == 0) {
                exit("data can't null'");
            } else {
                $this->_line[$sheet][] = $this->getLine($array);
            }
        } else {
            $this->_line[$sheet][] = $this->getLine($array);
        }
    }

    /**
     * @desc 设置工作簿的表头对象
     * @param array $head 表头数据
     * @param string $sheet 工作簿名称
     */
    public function setSheetHead($head, $sheet = "Sheet1")
    {
        $this->_line[$sheet][] = $this->getLine($head);
    }

    /**
     * @desc 添加多行 支持嵌套数组
     * @param array $array
     * @param string $sheet
     */
    public function addRows($array, $sheet = "Sheet1")
    {
        foreach ($array as $value) {
            if (is_array($value)) {
                $this->addRow($value, $sheet);
            } else {
                $this->addRow($array, $sheet);
            }
        }
    }

    /**
     * @desc 获取制定工作薄的列宽度
     * @param string $sheet 工作薄名称
     */
    public function getColumnLength($sheet = "Sheet1")
    {
        return $this->_title[$sheet]['width'];
    }

    /**
     * @desc 添加工作薄
     * @param string $sheet
     * @param array $array
     */
    public function addSheet($sheet, $array = array())
    {
        $this->_line[$sheet][] = $array;
    }

    /**
     * @desc 工作薄添加标题
     * @param string $str 标题
     * @param string $sheet 工作薄名
     */
    public function addTitle($str, $sheet = "Sheet1")
    {
        $str = $this->convert($str);
        $this->_title[$sheet]['title'] = "<Row ss:AutoFitHeight=\"0\" ss:StyleID=\"s65\">"
                                       . "<Cell ss:MergeAcross=\"{num}\"><Data ss:Type=\"String\">"
                                       . "{$str}</Data></Cell></Row>";
    }

    /**
     * @desc excel导出
     * @param string $fileName 导出的文件名
     * @param boolean $isConvert是否需要编码转换
     */
    public function export($fileName = "excel", $isConvert = false)
    {
        if ($isConvert) {
            $fileName = $this->getConvertString($fileName);
        }
        $fileName .= date("Y-m-d");
        header("Content-Type: application/vnd.ms-excel; charset=" . $this->_charset);
        header("Content-Disposition: filename=\"" . $fileName . date("Y-m-d") . ".xls\"");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
        header("Pragma: public");
        echo $this->getContent();

    }

    /**
     * 生成excel文件返回
     */
    public function getContent()
    {
        $content .=  stripslashes(sprintf($this->_header, $this->_charset));
        $content .= "<Styles>";
        $content .= stripslashes(sprintf($this->_alignDefault, $this->_align));
        $content .= $this->_alignLeft;
        $content .= $this->_alignRight;
        $content .= $this->_alignCenter;
        $content .= $this->_alignBold;
        $content .= "</Styles>";
        $_hasData = count($this->_line) == 0 ? false : true;
        if ($_hasData) {
            #有数据,解析数组对象到excel表格
            foreach ($this->_line as $key=>$value) {
                $content .= "<Worksheet ss:Name=\"{$this->convert($key)}\">"
                          . "<Table ss:DefaultColumnWidth=\"{$this->_defaultWidth}\" "
                          . "ss:DefaultRowHeight=\"{$this->_defaultHeight}\">";
                #列样式和宽度
                if (isset($this->_column[$key]['style_width'])) {
                    foreach ($this->_column[$key]['style_width'] as $s_key => $s_value) {
                        $content .= "<Column ss:Index=\"{$s_key}\" ss:AutoFitWidth=\"1\" ss:Width=\"$s_value\"/>";
                    }
                }
                #表格标题
                if (!empty($this->_title[$key]['title'])) {
                    $content .= str_replace("{num}",$this->_title[$key]['width'], $this->_title[$key]['title']);
                }
                #单元格
                foreach ($value as $_v) {
                    $content .= $_v;
                }
                $content .= "</Table></Worksheet>";
            }
                #加载标准工作薄(默认三个工作簿)
                $length = count($this->_line);
                while ($length<3) {
                    $length++;
                    $content .= "<Worksheet ss:Name=\"Sheet{$length}\"><Table></Table></Worksheet>";
                }
        } else {
            #无数据,添加默认工作簿和数据支持(错误处理:文件读取失败)
            for ($index=1; $index<=3; $index++) {
                $content .= "<Worksheet ss:Name=\"Sheet{$index}\"><Table></Table></Worksheet>";
            }
        }
        $content .= $this->_footer;

        return $content;
    }

    /**
     * 保存文件到指定目录
     * @param string $path 路径
     * @param string $filename 文件名
     */
    public function save($path, $filename)
    {
        $content = $this->getContent();
        $fname = $filename . "-" . date("Y-m-d") . ".xls";
        //创建文件,$fname为文件名
        $of = fopen($path . $fname, 'w');
        if ($of) {
            //把内容写入文件里
            fwrite($of,$content);
        }
        fclose($of);
    }

    /**
     * @desc excel导入函数,注该函数的文件名必须是非中文
     * @param unknown_type $fileName 导入的文件
     * @param unknown_type $convert_callback_function 回调函数 支持编码转换,需返回转换后的字符串
     * @return 三维数组,分别对应 工作薄/行/单元格
     */
    public function import($fileName, $convert_callback_function = null)
    {
        $xls = simplexml_load_file($fileName);
        if (!$xls) throw new IG_Exception('File is not exsit');
        $is_convert = !empty($convert_callback_function) && function_exists($convert_callback_function);
        $index = 0;
        $_ra = array();
        foreach ($xls->Worksheet as $worksheet) {#循环工作薄
            $index_i = 1;
            foreach ($worksheet->Table->Row as $cells) {#循环行
                if ($index_i !== 1) {
                    foreach ($cells as $cell) {#循环单元格
                        $_ra[$index][$index_i][] = $is_convert ?
                            call_user_func($convert_callback_function, $cell->Data) : $cell->Data;
                    }
                }
                $index_i++;
            }
            $index++;
        }
        return $_ra;
    }

    /**
    * @desc 设置字符编码
    * @param string $charset 设置导出文件的编码
    */
    public function setCharset($charset = "GBK")
    {
        $this->charset = $charset;
    }

    /**
    * 设置工作薄的列的宽度 array(1=>10,2=>23,3=>23,4=>213,5=>asd) 重复设置该值 将覆盖前一次操作的结果
    * @param string $sheet 工作薄名
    * @param array $array 列数组
    */
    public function setColumnWidth($sheet = "sheet1", $array)
    {
        if (!empty($this->_column[$sheet]['style_width']) && isset($this->_column[$sheet]['style_width'])) {
            unset($this->_column[$sheet]['style_width']);
        }
        $this->_column[$sheet]['style_width'] = $array;
    }

    /**
    * @desc 设置所有工作薄的列宽度
    * @param array $array 列宽度
    */
    public function setAllColumnWidth($array)
    {
        $_temp = $this->getAllSheetNames();
        foreach ($_temp as $value) {
            $this->setColumnWidth($value, $array);
        }
    }

    /**
    * @desc 设置默认行高
    * @param integer $height
    */
    public function setDefaultRowHeight($height = "54")
    {
        $this->_defaultHeight = $height;
    }

    /**
    * 设置字符编码转换函数(回调函数)
    * @param string $convert 设置转换函数 默认名称为convert
    */
    public function addConvert($convert = "convert")
    {
        $this->_convert = $convert;
    }

    /**
    * @desc 内部回调函数，完成字符编码的转化
    * @param unknown_type $str
    */
    protected function convert($str)
    {
        if (function_exists($this->convert)) {
            return call_user_func($this->convert, $str);
        } else {
            return $str;
        }
    }

    /**
    * 获取工作薄个数
    * @param int $sheet 获取工作薄的个数
    * @return integer
    */
    public function getSheets()
    {
        return sizeof($this->_line);
    }

    /**
    * 获取工作薄表格行数
    * @param String $sheet 工作薄名
    * @return integer
    */
    public function getRows($sheet)
    {
        return sizeof($this->_line[$sheet]);
    }

    /**
    * @desc 获取指定工作薄的表头信息
    * @param string $sheet 工作薄名称
    */
    public function getHead($sheet)
    {
        return $this->_heads[$sheet][0];
    }

    /**
    * @desc 设置默认行高度
    * @param integer $defaultHeight 行的默认高度 无默认值
    */
    public function setDefaultHeight($defaultHeight) {
        $this->_defaultHeight = $defaultHeight;
    }

    /**
    * @desc 设置默认的列宽度
    * @param integer $defaultWidth 列的默认宽度 无默认值
    */
    public function setDefaultWidth($defaultWidth) {
        $this->_defaultWidth = $defaultWidth;
    }

    /**
    * @desc 当前工作薄可用行数
    */
    public function currentSheetsLength()
    {
        return sizeof($this->_line)+1;
    }

    /**
    * @desc 设置默认的居中方式
    * @param string $_align 可选值 Left(left),Center(center),Right(right)
    */
    public function setDefaultAlign($_align)
    {
        $this->_align = ucfirst($_align);
    }

    /**
    * @desc 自动创建工作薄,支持自动分卷技术,该方法与addHead冲突,使用该方法时请勿调用addHead,否则将添加一个空白的工作薄
    * @param array $head 表头
    * @param array $data 数据
    * @param int $pageSize 页面行数 默认60000
    * @param string $defaultName 工作薄名,工作簿不能重名
    */
    public function addPageRow(array $head, array $data, $pageSize = 60000, $defaultName = "Sheet")
    {
        if (!isset($defaultName) || $defaultName == "Sheet")
            $defaultName = "Sheet" . ($this->getSheets()+1);

        if (empty(self::$_preWorkBook)) {
            self::$_preWorkBook = $defaultName;
            if (!isset($this->_heads[self::$_preWorkBook][0])) {
                $this->addHead($head, self::$_preWorkBook);
                $this->addRow($data, self::$_preWorkBook);
            }
        } else {
            if ($this->getRows(self::$_preWorkBook) >= ($pageSize+1)) {
                $this->addHead($head, $defaultName);
                $this->addRow($data, $defaultName);
                self::$pre_workBook = $defaultName;
            } else {
                $this->addRow($data, self::$_preWorkBook);
            }
        }
    }

    /**
    * @desc 返回所有工作薄名
    * @param null
    */
    public function getAllSheetNames()
    {
        return $this->_sheets;
    }

    /**
    * @desc 设置所有表格标题(分卷) 默认为合并当前工作薄的所有列,并居中显示(粗体) 该方法必须在工作簿存在的情况下调用.
    * @param string $title 标题
    */
    public function setAllTitle($title)
    {
        $_temp = $this->getAllSheetNames();
        foreach ($_temp as $value) {
            $this->addTitle($title, $value);
        }
    }

    /**
    * @desc 编码转换函数
    * @param string $str 转换的字符串
    * @param string $source_code 原编码 默认UTF-8
    * @param string $target_code 目标编码 默认GBK
    */
    protected function getConvertString($str, $source_code = 'UTF-8', $target_code = 'GBK')
    {
        return !empty($str) && is_string($str) ? iconv($source_code, $target_code, $str) : $str;
    }

    /**
    * @desc 打印调试信息
    * @param null
    */
    public function debug($out = true)
    {
        if ($out) {
            var_dump($this->_line);
        } else {
            return $this->_line;
        }
    }
    /**
    * @desc 工作薄命名后缀 调用此方法将生成全局唯一工作薄名
    * @param $name 自定义工作薄名
    */
    public function uniqueName($name)
    {
        $size = $this->getSheets();
        if ($size == 0) {
            return $name;
        } else {
            return $name . $size;
        }
    }
    /**
     * 设置单位格数据类型,该方法需在填充数据前完成 数据类型参照指定版本的excel
     * @param unknown_type $_columnType 指定的键值对数组
     */
    public function setColumnType($_columnType)
    {
        $this->_columnType = $_columnType;
    }
}